﻿using NPOI.HPSF;
/*******************************************************************************
 * Copyright © 2016 李华丽 版权所有
 * Author: 李华丽
 * Description: 李华丽-快速开发平台
 * Website：http://www.cnblogs.com/lihuali
*********************************************************************************/
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Record.Crypto;
using NPOI.POIFS.Crypt;
using NPOI.POIFS.FileSystem;
using Spire.Xls;
using System.Security.Cryptography;

namespace Dotnet.Code
{
    public class ExcelHelper
    {
        private string _title;
        private string _sheetName;
        private string _filePath;

        /// <summary>
        /// 将DataTable数据转换为Excel格式的内存数据流。兼容Webt程序可以下载Excel文件
        /// </summary>
        /// <param name="dt">DataTable格式数据</param>
        /// <returns>内存流</returns>
        private MemoryStream DataTableToExcel(DataTable dt)
        {

            if (dt == null)
            {
                throw new ArgumentNullException("DataTable数据为空");
            }
            MemoryStream fileStream = new MemoryStream();
            HSSFWorkbook workBook = new HSSFWorkbook(fileStream);
            HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(string.IsNullOrEmpty(dt.TableName) == true ? "Sheet1" : dt.TableName);
            HSSFRow rowHeader = (HSSFRow)sheet.CreateRow(0);
            rowHeader.RowStyle.FillBackgroundColor = (short)1.2;
            return null;
        }



        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private bool ToExcel(DataTable table)
        {
            FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            this._sheetName = string.IsNullOrWhiteSpace(this._sheetName) ? "sheet1" : this._sheetName;
            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(this._title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();
            return true;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath)
        {
            this._title = title;
            this._sheetName = sheetName;
            this._filePath = filePath;
            return ToExcel(table);
        }
        public bool ToExcel(DataTable table, string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            this._sheetName = string.IsNullOrWhiteSpace(this._sheetName) ? "sheet1" : this._sheetName;
            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            //IRow row = sheet.CreateRow(0);
            //if (!string.IsNullOrEmpty(this._title))
            //{
            //    row.CreateCell(0).SetCellValue(this._title);
            //    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            //    row.Height = 500;
            //}


            //ICellStyle cellStyle = workBook.CreateCellStyle();
            //IFont font = workBook.CreateFont();
            //font.FontName = "微软雅黑";
            //font.FontHeightInPoints = 17;
            //cellStyle.SetFont(font);
            //cellStyle.VerticalAlignment = VerticalAlignment.Center;
            //cellStyle.Alignment = HorizontalAlignment.Center;
            //row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(1 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();
            return true;
        }
        public DataTable ExcelToDataTable(string fileName, string pwd = "")
        {
            try
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                Workbook workbook = new Workbook();
                if (string.IsNullOrEmpty(pwd) == false)
                {
                    workbook.OpenPassword = pwd;//文档密码
                }
                workbook.LoadFromFile(fileName);//加载excel文档
                if (workbook.Worksheets.Count == 0)
                    return null;
                Worksheet sheet = workbook.Worksheets[0];

                //表头
                CellRange header = sheet.Rows[0];
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastColumn; i++)
                {
                    string colName = header.Cells[i].DisplayedText.Trim();
                    if (string.IsNullOrEmpty(colName) == true)
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    else
                        dt.Columns.Add(new DataColumn(colName.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRow; i <= sheet.LastRow-1; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        CellRange cell = sheet.Rows[i].Cells[j];
                        bool isFormulaDateTimeExce = true;
                        string cellValue = "";
                        try {
                            cellValue = cell.DateTimeValue.ToString("yyyy-MM-dd HH:mm:ss");
                            if (cellValue.StartsWith("0001")) isFormulaDateTimeExce = false;
                        }
                        catch { isFormulaDateTimeExce = false; }

                        if (isFormulaDateTimeExce == false) //非时间串
                        {
                            cellValue = cell.DisplayedText.Trim();
                        }
                        dr[j] = cellValue;
                        if (string.IsNullOrEmpty(cellValue) == false)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 返回DataTable转化为Excel文档内存数据流。将表的字段名放在最后一行并作为隐藏行。以便导入数据时可以复用。
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="caption">列头  key:value=字段名:列头名</param>
        /// <param name="ignoreCols">要忽略的列</param>
        /// <param name="editCols">可编辑列</param>
        /// <returns>DataTable转化为Excel文档内存数据流</returns>
        public MemoryStream DataTableToExcelStream(DataTable dtSource, Dictionary<string, string> caption = null, string[] ignoreCols = null, string[] editCols = null, string[] hideCols = null)
        {
            if (dtSource == null || dtSource.Rows.Count == 0)
            {
                throw new ArgumentNullException("数据源为空,不支持导出Excel");
            }
            try
            {
                if (ignoreCols != null)
                {
                    foreach (string colName in ignoreCols)
                    {
                        dtSource.Columns.Remove(colName);
                    }
                }
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "武汉明源-李华丽";
                hssfworkbook.DocumentSummaryInformation = dsi;
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "武汉明源";
                hssfworkbook.SummaryInformation = si;
                //设置单元格样式。锁定和背景颜色
                HSSFCellStyle unlockStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); //设置不锁定。
                unlockStyle.IsLocked = false;
                HSSFCellStyle bgStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();  //设置背景颜色
                bgStyle.FillBackgroundColor = (short)3.0; // NPOI.HSSF.Util.HSSFColor.Red;
                HSSFCellStyle hideStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
                hideStyle.IsHidden = false;
                //创建Sheet
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");
                //李华丽:2018-08-10 14:20:32  处理列头
                HSSFRow rowHead = (HSSFRow)sheet.CreateRow(0);
                rowHead.RowStyle = bgStyle;
                for (int i = 0; i < dtSource.Columns.Count; i++)  //设置Caption为列头
                {
                    string colName = dtSource.Columns[i].ColumnName;
                    if (caption == null)
                    {
                        rowHead.CreateCell(i).SetCellValue(colName);
                    }
                    else
                    {
                        string captionName = "";
                        bool ret = caption.TryGetValue(colName, out captionName);
                        if (ret == true)
                        {
                            rowHead.CreateCell(i).SetCellValue(captionName);
                        }
                        else
                        {
                            rowHead.CreateCell(i).SetCellValue(colName);
                        }
                    }
                    rowHead.Height = 450;
                    sheet.AutoSizeColumn(i);
                }

                //李华丽:2018-08-10 14:22:44 处理数据内容
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1); //从第一行处创建ROW,第一行为列头
                    row.Height = 250;
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        HSSFCell cell = (HSSFCell)row.CreateCell(j);
                        if (editCols != null && editCols.Contains(dtSource.Columns[j].ColumnName) == true)
                        {
                            cell.CellStyle = unlockStyle;
                        }
                        else
                        {
                            cell.CellStyle = bgStyle;
                        }
                        string value = dtSource.Rows[i][j] == null ? "" : dtSource.Rows[i][j].ToString();
                        cell.SetCellValue(value);
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }
                //最后一行保存隐藏的数据表字段信息，以便于导入数据时使用
                HSSFRow rowFooter = (HSSFRow)sheet.CreateRow(dtSource.Rows.Count + 1);
                rowFooter.RowStyle = unlockStyle;
                rowFooter.RowStyle = hideStyle;
                rowFooter.ZeroHeight = true;
                for (int i = 0; i < dtSource.Columns.Count; i++)  //设置Caption为列头
                {
                    rowFooter.CreateCell(i).SetCellValue(dtSource.Columns[i].ColumnName);
                    rowFooter.Height = 450;
                    sheet.AutoSizeColumn(i);
                }
                sheet.ProtectSheet("lihuali");
                MemoryStream file = new MemoryStream();
                hssfworkbook.Write(file);
                return file;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + ex.StackTrace);
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            IWorkbook workbook = null;
            //string fileName = null; //文件名
            FileStream fs = null;

            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (!string.IsNullOrEmpty(cellValue))
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 读取.xlsx文件内容，控制台输出
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetName">工作簿名称</param>
        /// <param name="password">密码</param>
        public void ReadFormExcelFile(string FilePath, string SheetName, string password)
        {
            IWorkbook wk = null;

            try
            {
                FileStream fileStream = File.OpenRead(FilePath);
                POIFSFileSystem fs = new POIFSFileSystem(fileStream);
                
                    EncryptionInfo info = new EncryptionInfo(fs);
                    Decryptor decryptor = Decryptor.GetInstance(info);
                    bool isPasswordCorrect = decryptor.VerifyPassword(password);
                    var stream = decryptor.GetDataStream(fs);

                    wk = new XSSFWorkbook(stream);

                

                ISheet sheet = wk.GetSheet(SheetName);
                IRow row = sheet.GetRow(0);

                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);
                    if (row != null)
                    {
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            string value = row.GetCell(j).ToString();
                            Console.Write(value + " ");
                        }
                        Console.WriteLine();
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }
}
